import mysql from 'mysql2/promise';
import dbConfig from '../config/database.js';
import logger from '../utils/logger.js';

class DatabaseService {
  constructor() {
    this.pool = null;
    this.init();
  }

  async init() {
    try {
      this.pool = mysql.createPool({
        ...dbConfig,
        waitForConnections: true,
        connectionLimit: dbConfig.connectionLimit || 10,
        queueLimit: 0
      });

      // 测试连接（带重试机制）
      await this.testConnection(3);
      
      // 执行初始化脚本（创建表）
      await this.initTables();
      
    } catch (error) {
      logger.error('数据库初始化失败:', error);
      if (this.pool) {
        await this.pool.end();
        this.pool = null;
      }
      throw new Error(`数据库初始化失败: ${error.message}`);
    }
  }

  async testConnection(retries = 3) {
    for (let attempt = 1; attempt <= retries; attempt++) {
      try {
        const connection = await this.pool.getConnection();
        await connection.ping();
        connection.release();
        logger.info(`数据库连接成功 (尝试 ${attempt}/${retries})`);
        return;
      } catch (error) {
        logger.warn(`数据库连接失败 (尝试 ${attempt}/${retries}): ${error.message}`);
        if (attempt === retries) {
          throw error;
        }
        // 等待后重试
        await new Promise(resolve => setTimeout(resolve, 2000 * attempt));
      }
    }
  }

  async initTables() {
    try {
      // 读取并执行初始化SQL脚本
      const fs = await import('fs/promises');
      const path = await import('path');
      
      const sqlPath = path.resolve('./database/init.sql');
      
      // 检查文件是否存在
      try {
        await fs.access(sqlPath);
      } catch (accessError) {
        logger.warn(`SQL初始化文件不存在: ${sqlPath}`);
        return;
      }
      
      const sqlContent = await fs.readFile(sqlPath, 'utf8');
      
      // 分割SQL语句（按分号分割，忽略空行和注释）
      const statements = sqlContent
        .split(';')
        .map(stmt => stmt.trim())
        .filter(stmt => stmt.length > 0 && !stmt.startsWith('--') && !stmt.startsWith('#'));
      
      let successCount = 0;
      let errorCount = 0;
      
      for (const statement of statements) {
        if (statement.length > 0) {
          try {
            await this.pool.execute(statement);
            successCount++;
          } catch (stmtError) {
            errorCount++;
            logger.warn(`SQL语句执行失败: ${stmtError.message}`, { statement: statement.substring(0, 100) });
          }
        }
      }
      
      logger.info(`数据库表初始化完成: ${successCount}个成功, ${errorCount}个失败`);
    } catch (error) {
      logger.warn('数据库表初始化警告:', error.message);
      // 不抛出错误，继续运行
    }
  }

  // 房间相关数据库操作
  async createRoom(roomData) {
    const { id, name, hostId, playerCount, maxPlayers, gameConfig } = roomData;
    
    if (!this.pool) {
      throw new Error('数据库连接池未初始化');
    }

    let connection;
    try {
      connection = await this.pool.getConnection();
      await connection.beginTransaction();
      
      // 插入房间记录
      await connection.execute(
        'INSERT INTO game_rooms (id, name, host_id, status, player_count, max_players, game_config) VALUES (?, ?, ?, ?, ?, ?, ?)',
        [id, name, hostId, 'waiting', playerCount, maxPlayers, JSON.stringify(gameConfig)]
      );
      
      // 插入房主作为第一个玩家
      await connection.execute(
        'INSERT INTO room_players (room_id, player_id, player_name, seat_index, is_ready, status) VALUES (?, ?, ?, ?, ?, ?)',
        [id, hostId, roomData.hostName, 0, false, 'online']
      );
      
      await connection.commit();
      logger.info(`房间创建成功: ${id}`);
      
      return true;
    } catch (error) {
      if (connection) {
        try {
          await connection.rollback();
        } catch (rollbackError) {
          logger.error(`回滚事务失败: ${rollbackError.message}`);
        }
      }
      logger.error(`房间创建失败 - 错误码: ${error.code}, 消息: ${error.message}`, {
        roomId: id,
        hostId,
        sqlState: error.sqlState
      });
      throw new Error(`房间创建失败: ${this.getSafeErrorMessage(error)}`);
    } finally {
      if (connection) {
        connection.release();
      }
    }
  }

  async getRoomById(roomId) {
    if (!this.pool) {
      throw new Error('数据库连接池未初始化');
    }

    let connection;
    try {
      connection = await this.pool.getConnection();
      const [rooms] = await connection.execute(
        'SELECT * FROM game_rooms WHERE id = ?',
        [roomId]
      );
      
      if (rooms.length === 0) {
        return null;
      }
      
      const room = rooms[0];
      
      // 转换字段名为驼峰命名
      const roomData = {
        id: room.id,
        name: room.name,
        hostId: room.host_id,  // 重要: 转换字段名
        status: room.status,
        playerCount: room.player_count,
        maxPlayers: room.max_players,
        gameConfig: room.game_config,
        createdAt: room.created_at,
        updatedAt: room.updated_at
      };
      
      // 获取房间玩家
      const [players] = await connection.execute(
        'SELECT * FROM room_players WHERE room_id = ? ORDER BY seat_index',
        [roomId]
      );
      
      roomData.players = players.map(player => ({
        id: player.player_id,
        name: player.player_name,
        seatIndex: player.seat_index,
        isReady: Boolean(player.is_ready),
        status: player.status,
        socketId: player.socket_id,
        joinedAt: player.joined_at,
        isAI: Boolean(player.is_ai) // 添加AI标识
      }));
      
      return roomData;
    } catch (error) {
      logger.error(`获取房间失败 (${roomId}): ${error.message}`, { 
        roomId,
        errorCode: error.code,
        sqlState: error.sqlState 
      });
      throw new Error(`获取房间失败: ${this.getSafeErrorMessage(error)}`);
    } finally {
      if (connection) {
        connection.release();
      }
    }
  }

  async getAvailableRooms(limit = 20) {
    if (!this.pool) {
      throw new Error('数据库连接池未初始化');
    }

    try {
      // 使用最简单直接的方法，不使用任何预处理语句
      const connection = await this.pool.getConnection();
      
      const [rooms] = await connection.query(
        "SELECT id, name, status, max_players, created_at FROM game_rooms WHERE status = 'waiting' AND player_count < max_players ORDER BY created_at DESC LIMIT 20"
      );
      
      connection.release();
      
      // 返回简化的数据结构
      const result = rooms.map(room => ({
        id: room.id,
        name: room.name,
        status: room.status,
        playerCount: 1, // 暂时硬编码，因为已知房间都有1个玩家
        maxPlayers: room.max_players,
        createdAt: room.created_at
      }));
      
      return result;
    } catch (error) {
      logger.error(`获取可用房间失败: ${error.message}`, error.stack);
      throw error;
    }
  }

  async joinRoom(roomId, playerId, playerName, isAI = false) {
    if (!this.pool) {
      throw new Error('数据库连接池未初始化');
    }

    let connection;
    try {
      connection = await this.pool.getConnection();
      await connection.beginTransaction();
      
      // 检查房间状态和人数
      const [rooms] = await connection.execute(
        'SELECT * FROM game_rooms WHERE id = ? AND status = "waiting"',
        [roomId]
      );
      
      if (rooms.length === 0) {
        throw new Error('房间不存在或已开始游戏');
      }
      
      const room = rooms[0];
      
      // 检查玩家是否已在房间中
      const [existingPlayers] = await connection.execute(
        'SELECT * FROM room_players WHERE room_id = ? AND player_id = ?',
        [roomId, playerId]
      );
      
      if (existingPlayers.length > 0) {
        // 更新玩家状态为在线
        await connection.execute(
          'UPDATE room_players SET status = "online", last_seen = CURRENT_TIMESTAMP WHERE room_id = ? AND player_id = ?',
          [roomId, playerId]
        );
      } else {
        // 检查房间是否已满
        const [playerCount] = await connection.execute(
          'SELECT COUNT(*) as count FROM room_players WHERE room_id = ? AND status != "disconnected"',
          [roomId]
        );
        
        if (playerCount[0].count >= room.max_players) {
          throw new Error('房间已满');
        }
        
        // 找到可用的座位 - 修复：disconnected状态的玩家应该让出座位
        const [usedSeats] = await connection.execute(
          'SELECT seat_index FROM room_players WHERE room_id = ? AND status IN (?, ?)',
          [roomId, 'online', 'offline']
        );
        
        const occupiedSeats = new Set(usedSeats.map(s => s.seat_index));
        let availableSeat = -1;
        for (let i = 0; i < room.max_players; i++) {
          if (!occupiedSeats.has(i)) {
            availableSeat = i;
            break;
          }
        }
        
        if (availableSeat === -1) {
          throw new Error('没有可用座位');
        }
        
        // 清理disconnected状态的同座位玩家
        await connection.execute(
          'DELETE FROM room_players WHERE room_id = ? AND seat_index = ? AND status = ?',
          [roomId, availableSeat, 'disconnected']
        );
        
        // 插入新玩家 - 支持AI玩家
        await connection.execute(
          'INSERT INTO room_players (room_id, player_id, player_name, seat_index, is_ready, status, is_ai) VALUES (?, ?, ?, ?, ?, ?, ?)',
          [roomId, playerId, playerName, availableSeat, isAI, 'online', isAI ? 1 : 0]
        );
      }
      
      // 更新房间玩家数量 - 修复：只统计在线和离线玩家
      await connection.execute(
        'UPDATE game_rooms SET player_count = (SELECT COUNT(*) FROM room_players WHERE room_id = ? AND status IN (?, ?)) WHERE id = ?',
        [roomId, 'online', 'offline', roomId]
      );
      
      await connection.commit();
      logger.info(`玩家 ${playerName} 加入房间 ${roomId}`);
      
      return true;
    } catch (error) {
      if (connection) {
        try {
          await connection.rollback();
        } catch (rollbackError) {
          logger.error(`回滚事务失败: ${rollbackError.message}`);
        }
      }
      logger.error(`玩家加入房间失败: ${error.message}`, {
        roomId,
        playerId,
        playerName,
        errorCode: error.code,
        sqlState: error.sqlState
      });
      throw new Error(this.getSafeErrorMessage(error));
    } finally {
      if (connection) {
        connection.release();
      }
    }
  }

  async updatePlayerStatus(playerId, roomId, status, socketId = null) {
    const connection = await this.pool.getConnection();
    try {
      const updateData = [status, playerId, roomId];
      let sql = 'UPDATE room_players SET status = ?, last_seen = CURRENT_TIMESTAMP';
      
      if (socketId !== null) {
        sql += ', socket_id = ?';
        updateData.splice(1, 0, socketId);
      }
      
      sql += ' WHERE player_id = ? AND room_id = ?';
      
      const [result] = await connection.execute(sql, updateData);
      
      if (result.affectedRows === 0) {
        logger.warn(`更新玩家状态失败: 玩家不存在`, { playerId, roomId, status });
        return false;
      }
      
      logger.debug(`更新玩家状态成功: ${playerId} -> ${status} (${result.affectedRows} 行受影响)`);
      return true;
    } catch (error) {
      logger.error(`更新玩家状态失败 (${playerId}): ${error.message}`, { 
        playerId, 
        roomId, 
        status, 
        errorCode: error.code,
        sqlState: error.sqlState 
      });
      throw new Error(`更新玩家状态失败: ${error.message}`);
    } finally {
      connection.release();
    }
  }

  async updatePlayerReady(playerId, roomId, isReady) {
    try {
      await this.pool.execute(
        'UPDATE room_players SET is_ready = ? WHERE player_id = ? AND room_id = ?',
        [isReady, playerId, roomId]
      );
      logger.debug(`更新玩家准备状态: ${playerId} -> ${isReady}`);
    } catch (error) {
      logger.error(`更新玩家准备状态失败: ${error.message}`);
      throw error;
    }
  }

  async leaveRoom(playerId, roomId) {
    const connection = await this.pool.getConnection();
    try {
      await connection.beginTransaction();
      
      // 删除玩家记录
      await connection.execute(
        'DELETE FROM room_players WHERE player_id = ? AND room_id = ?',
        [playerId, roomId]
      );
      
      // 更新房间玩家数量
      const [playerCount] = await connection.execute(
        'SELECT COUNT(*) as count FROM room_players WHERE room_id = ?',
        [roomId]
      );
      
      const count = playerCount[0].count;
      
      if (count === 0) {
        // 房间为空，删除房间
        await connection.execute('DELETE FROM game_rooms WHERE id = ?', [roomId]);
        logger.info(`房间 ${roomId} 已删除（无玩家）`);
      } else {
        // 更新房间玩家数量
        await connection.execute(
          'UPDATE game_rooms SET player_count = ? WHERE id = ?',
          [count, roomId]
        );
        
        // 检查是否需要转移房主
        const [rooms] = await connection.execute(
          'SELECT host_id FROM game_rooms WHERE id = ?',
          [roomId]
        );
        
        if (rooms.length > 0 && rooms[0].host_id === playerId) {
          // 转移房主给第一个在线玩家
          const [players] = await connection.execute(
            'SELECT player_id FROM room_players WHERE room_id = ? AND status = "online" ORDER BY joined_at LIMIT 1',
            [roomId]
          );
          
          if (players.length > 0) {
            await connection.execute(
              'UPDATE game_rooms SET host_id = ? WHERE id = ?',
              [players[0].player_id, roomId]
            );
            logger.info(`房主已转移: ${roomId} -> ${players[0].player_id}`);
          }
        }
      }
      
      await connection.commit();
      logger.info(`玩家 ${playerId} 离开房间 ${roomId}`);
      
      return count;
    } catch (error) {
      await connection.rollback();
      logger.error(`玩家离开房间失败: ${error.message}`);
      throw error;
    } finally {
      connection.release();
    }
  }

  async updateRoomStatus(roomId, status) {
    try {
      await this.pool.execute(
        'UPDATE game_rooms SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        [status, roomId]
      );
      logger.debug(`更新房间状态: ${roomId} -> ${status}`);
    } catch (error) {
      logger.error(`更新房间状态失败: ${error.message}`);
      throw error;
    }
  }

  // 游戏会话相关操作
  async createGameSession(sessionData) {
    try {
      const { id, roomId, gameState, currentPlayerIndex, currentRound } = sessionData;
      
      await this.pool.execute(
        'INSERT INTO game_sessions (id, room_id, game_state, current_player_index, current_round, status) VALUES (?, ?, ?, ?, ?, ?)',
        [id, roomId, JSON.stringify(gameState), currentPlayerIndex, currentRound, 'playing']
      );
      
      logger.info(`游戏会话创建: ${id}`);
      return true;
    } catch (error) {
      logger.error(`创建游戏会话失败: ${error.message}`);
      throw error;
    }
  }

  async updateGameSession(sessionId, updateData) {
    try {
      const { gameState, currentPlayerIndex, currentRound, roundCards, collectedCards } = updateData;
      
      await this.pool.execute(
        `UPDATE game_sessions SET 
         game_state = ?, 
         current_player_index = ?, 
         current_round = ?, 
         round_cards = ?, 
         collected_cards = ?,
         updated_at = CURRENT_TIMESTAMP 
         WHERE id = ?`,
        [
          JSON.stringify(gameState),
          currentPlayerIndex,
          currentRound,
          roundCards ? JSON.stringify(roundCards) : null,
          collectedCards ? JSON.stringify(collectedCards) : null,
          sessionId
        ]
      );
      
      logger.debug(`游戏会话更新: ${sessionId}`);
    } catch (error) {
      logger.error(`更新游戏会话失败: ${error.message}`);
      throw error;
    }
  }

  async getGameSession(sessionId) {
    try {
      const [sessions] = await this.pool.execute(
        'SELECT * FROM game_sessions WHERE id = ?',
        [sessionId]
      );
      
      return sessions.length > 0 ? sessions[0] : null;
    } catch (error) {
      logger.error(`获取游戏会话失败: ${error.message}`);
      throw error;
    }
  }

  // 记录游戏操作
  async recordGameAction(actionData) {
    try {
      const { sessionId, playerId, actionType, actionData: data, roundNumber, sequence } = actionData;
      
      await this.pool.execute(
        'INSERT INTO game_actions (session_id, player_id, action_type, action_data, round_number, sequence) VALUES (?, ?, ?, ?, ?, ?)',
        [sessionId, playerId, actionType, JSON.stringify(data), roundNumber, sequence]
      );
      
    } catch (error) {
      logger.error(`记录游戏操作失败: ${error.message}`);
      // 不抛出错误，避免影响游戏进行
    }
  }

  // 清理过期数据
  async cleanupExpiredData() {
    const connection = await this.pool.getConnection();
    try {
      await connection.beginTransaction();
      
      const cleanupOperations = [
        {
          name: '已结束游戏会话',
          sql: 'DELETE FROM game_sessions WHERE status = "finished" AND updated_at < DATE_SUB(NOW(), INTERVAL 24 HOUR)'
        },
        {
          name: '空房间',
          sql: 'DELETE FROM game_rooms WHERE player_count = 0 AND created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR)'
        },
        {
          name: '离线玩家状态',
          sql: 'UPDATE room_players SET status = "disconnected" WHERE status = "offline" AND last_seen < DATE_SUB(NOW(), INTERVAL 30 MINUTE)'
        },
        {
          name: '游戏操作记录',
          sql: 'DELETE FROM game_actions WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY)'
        }
      ];
      
      let totalAffected = 0;
      const results = {};
      
      for (const operation of cleanupOperations) {
        try {
          const [result] = await connection.execute(operation.sql);
          const affected = result.affectedRows || result.changedRows || 0;
          results[operation.name] = affected;
          totalAffected += affected;
          
          if (affected > 0) {
            logger.info(`清理${operation.name}: ${affected}条记录`);
          }
        } catch (opError) {
          logger.error(`清理${operation.name}失败: ${opError.message}`, {
            sql: operation.sql.substring(0, 100),
            errorCode: opError.code
          });
          // 继续执行其他清理操作
        }
      }
      
      await connection.commit();
      logger.info(`数据清理完成: 总共处理${totalAffected}条记录`, results);
    } catch (error) {
      await connection.rollback();
      logger.error(`数据清理失败: ${error.message}`, {
        errorCode: error.code,
        sqlState: error.sqlState
      });
      // 不抛出错误，避免影响正常服务
    } finally {
      connection.release();
    }
  }

  async close() {
    if (this.pool) {
      try {
        await this.pool.end();
        logger.info('数据库连接池已关闭');
      } catch (error) {
        logger.error(`关闭数据库连接池失败: ${error.message}`);
      } finally {
        this.pool = null;
      }
    }
  }

  // 获取安全的错误信息（过滤敏感信息）
  getSafeErrorMessage(error) {
    // 常见的数据库错误码映射
    const errorMapping = {
      'ER_DUP_ENTRY': '数据重复',
      'ER_NO_REFERENCED_ROW_2': '关联数据不存在',
      'ER_ROW_IS_REFERENCED_2': '数据被其他记录引用，无法删除',
      'ER_DATA_TOO_LONG': '数据长度超限',
      'ER_BAD_NULL_ERROR': '必填字段不能为空',
      'ECONNREFUSED': '数据库连接被拒绝',
      'ETIMEDOUT': '数据库连接超时',
      'ER_ACCESS_DENIED_ERROR': '数据库访问权限不足'
    };

    if (error.code && errorMapping[error.code]) {
      return errorMapping[error.code];
    }

    // 对于未知错误，返回通用错误信息
    if (error.message) {
      // 移除可能包含敏感信息的部分
      const safeMessage = error.message
        .replace(/password[^\s]*/gi, '[password]')
        .replace(/user[^\s]*=[^\s]*/gi, '[user]')
        .replace(/host[^\s]*=[^\s]*/gi, '[host]');
      return safeMessage;
    }

    return '数据库操作失败';
  }

  // 检查数据库连接状态
  async checkConnection() {
    if (!this.pool) {
      return false;
    }

    try {
      const connection = await this.pool.getConnection();
      await connection.ping();
      connection.release();
      return true;
    } catch (error) {
      logger.warn(`数据库连接检查失败: ${error.message}`);
      return false;
    }
  }
}

export default DatabaseService;